﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_FIM_MAS_Banks : IDAL_FIM_MAS_Banks
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public D_FIM_MAS_Banks()
        {

        }

        public IList<M_FIM_MAS_Banks> GetFull()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MAS_Banks> infoList = new List<M_FIM_MAS_Banks>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT *"
                        + " FROM FIM_MAS_BANKS"
                        + " WHERE RECORD_STATUS not like @delete_status";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_Banks info = new M_FIM_MAS_Banks();
                        info.bank_seq = Convert.ToInt32(drc[i]["Bank_seq"]);
                        info.bank_id = drc[i]["Bank_id"].ToString();
                        info.bank_name = drc[i]["Bank_name"].ToString();
                        info.bank_name_eng = drc[i]["Bank_name_eng"].ToString();

                        info.record_status = drc[i]["RECORD_STATUS"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["CREATED_BY"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["CREATED_WHEN"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["UPDATED_BY"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["UPDATED_WHEN"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MAS_Banks> GetList()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MAS_Banks> infoList = new List<M_FIM_MAS_Banks>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT BANK_SEQ, BANK_NAME"
                        + " FROM FIM_MAS_BANKS"
                        + " WHERE RECORD_STATUS not like @delete_status";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_Banks info = new M_FIM_MAS_Banks();
                        info.bank_seq = Convert.ToInt32(drc[i]["Bank_seq"]);
                        info.bank_name = drc[i]["Bank_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_MAS_Banks GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MAS_Banks info = new M_FIM_MAS_Banks();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT *"
                        + " FROM FIM_MAS_BANKS"
                        + " WHERE BANK_SEQ = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bank_seq = Convert.ToInt32(drc[0]["Bank_seq"]);
                    info.bank_id = drc[0]["Bank_id"].ToString();
                    info.bank_name = drc[0]["Bank_name"].ToString();
                    info.bank_name_eng = drc[0]["Bank_name_eng"].ToString();

                    info.record_status = drc[0]["RECORD_STATUS"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["CREATED_BY"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["CREATED_WHEN"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["UPDATED_BY"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["UPDATED_WHEN"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_FIM_MAS_Banks GetBankNamePaymentCheques(int? pay_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MAS_Banks info = new M_FIM_MAS_Banks();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@pay_seq", pay_seq);

                strSQL = "SELECT b.bank_name,b.bank_name_eng"
                    + " FROM fim_cheques c"
                    + " LEFT JOIN fim_mas_bank_accounts ba ON c.bank_acc_seq = ba.bank_acc_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON ba.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_payment_cheques pc ON c.cheque_seq = pc.cheque_seq"
                    + " WHERE c.record_status not like @delete_status"
                    + " AND pc.record_status not like @delete_status"
                        + (pay_seq != null ? " AND pc.pay_seq = @pay_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {                   
                    
                    info.bank_name = drc[0]["bank_name"].ToString();
                    info.bank_name_eng = drc[0]["bank_name_eng"].ToString();                     
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_FIM_MAS_Banks info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@Bank_id", info.bank_id);
                dbManager.AddParameters(1, "@Bank_name", info.bank_name);
                dbManager.AddParameters(2, "@Bank_name_eng", info.bank_name_eng);

                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@created_by", info.created_by);
                dbManager.AddParameters(5, "@created_when", info.created_when);

                strSQL = "INSERT INTO FIM_MAS_BANKS"
                    + " (Bank_id, Bank_name, Bank_name_eng"
                    + " , RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@Bank_id"
                    + ", @Bank_name"
                    + ", @Bank_name_eng"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " );"
                    + " select scope_identity()";
                var strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                 
                return strResult;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_MAS_Banks info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@Bank_seq", info.bank_seq);
                dbManager.AddParameters(1, "@Bank_id", info.bank_id);
                dbManager.AddParameters(2, "@Bank_name", info.bank_name);
                dbManager.AddParameters(3, "@Bank_name_eng", info.bank_name_eng);

                dbManager.AddParameters(4, "@updated_by", info.updated_by);
                dbManager.AddParameters(5, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_mas_banks"
                    + " SET bank_id = @Bank_id"
                    + ", bank_name = @Bank_name"
                    + ", bank_name_eng = @Bank_name_eng"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bank_seq = @Bank_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_MAS_Banks info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Bank_seq", info.bank_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_mas_banks"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bank_seq = @Bank_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
